﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Web.UI;
using System.Data.SqlClient;
using Betop.Lib.Data;
using System.Text.RegularExpressions;
using System.Reflection;
using Betop.Lib.Utilities;
using Betop.Lib.Enums;
using Betop.Lib.Web;

namespace Betop.Lib.Core.Base
{
    public abstract class CommonOperate<Info> : ICommonOperate<Info> where Info : class,new()
    {
        private string _tableName;
        private string _pkid;

        protected abstract BaseDAL DAL
        {
            get;
        }

        public DBHelper dbOper
        {
            get
            {
                return DAL.dbOper;
            }
            set
            {
                DAL.dbOper = value;
            }
        }


        public CommonOperate()
        {

            //RegisterInfo info = RegisterBO.GetInfo();
            //if (info == null || info.ExpireDate < DateTime.Now)
            //{
            //    throw new Exception("你的产品已经过期了,请与商家联系");
            //}
            Info info = new Info();
            if (info is EntityBase)
            {
                _tableName = "[" + (info as EntityBase).GetTableName() + "]";
            }
            else
            {
                Type infoType = typeof(Info);
                BindingFlags flag = BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.Public;

                _tableName = "[" + (string)infoType.GetField(Constants.TABLE_NAME, flag).GetValue(null) + "]";

            }

            _pkid = TableField.ID.ToString();
        }

        public CommonOperate(string tableName)
        {
            _tableName = "[" + tableName.ToString() + "]";
            _pkid = TableField.ID.ToString();
        }

        #region IBase<Info> 成员

        public virtual void Delete(object id)
        {
            string strSql = "delete from " + _tableName + " where " + _pkid + " = @ID";
            SqlParameter para = new SqlParameter("@ID", id);
            dbOper.RunSql(strSql, para);
        }

        public virtual void Delete(string ids, bool isInt)
        {
            if (isInt)
            {
                Delete(StrUtil.SplitToInt(ids));
            }
            else
            {
                Delete(StrUtil.SplitToStr(ids));
            }
        }

        /// <summary>
        /// 批量删除一批数据
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="ids"></param>
        public virtual void Delete(IEnumerable<int> ids)
        {
            string strIDs = ConvertUtil.ConvertToDbStr(ids);

            string strSql = "delete from " + _tableName + " where " + _pkid + " in (" + strIDs + ")";

            dbOper.RunSql(strSql);
        }

        public virtual void Delete(IEnumerable<string> ids)
        {
            string strIDs = ConvertUtil.ConvertToDbStr(ids);
            string strSql = "delete from " + _tableName + " where " + _pkid + " in (" + strIDs + ")";
            dbOper.RunSql(strSql);
        }

        public virtual DataTable GetDataTable()
        {
            string sql = "select * from " + _tableName;
            DataTable dt = dbOper.Query(sql);
            return dt;
        }

        //public virtual DataTable GetDataTable(IEnumerable<int

        public virtual DataTable GetDataTable(PagerSortSetting pagerSetting)
        {
            return GetDataTable(pagerSetting, null);
        }

        public virtual DataTable GetDataTable(PagerSortSetting pagerSetting, string condition, params object[] conditionParas)
        {
            string sql = GetPagerSql(pagerSetting, condition, conditionParas);
            DataTable dt = dbOper.Query(sql, conditionParas);
            return dt;
        }

        public virtual List<Info> GetList()
        {
            string strSql = "select * from " + _tableName;
            List<Info> list = dbOper.Query<Info>(strSql);
            return list;
        }



        public virtual List<Info> GetList(IEnumerable<int> ids)
        {
            string sql = "select * from " + _tableName + " where " + _pkid + " in (" + ConvertUtil.ConvertToDbStr(ids) + ")";
            List<Info> list = dbOper.Query<Info>(sql);
            return list;
        }



        public virtual List<Info> GetList(IEnumerable<long> ids)
        {
            string sql = "select * from " + _tableName + " where " + _pkid + " in (" + ConvertUtil.ConvertToDbStr(ids) + ")";
            List<Info> list = dbOper.Query<Info>(sql);
            return list;
        }

        public virtual List<Info> GetList(IEnumerable<string> ids)
        {
            string sql = "select * from " + _tableName + " where " + _pkid + " in (" + ConvertUtil.ConvertToDbStr(ids) + ")";
            List<Info> list = dbOper.Query<Info>(sql);
            return list;
        }

        public virtual List<Info> GetList(string where, params object[] paras)
        {
            string sql = "select * from " + _tableName;
            if (!string.IsNullOrEmpty(where))
            {
                sql += " where " + where;
            }
            List<Info> list = dbOper.Query<Info>(sql, paras);
            return list;
        }

        /// <summary>
        /// 主要针对mvc的一个轻量级的获取带翻页列表的方法
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="conditionParas"></param>
        /// <returns></returns>
        public virtual List<Info> GetList(PagerSetting pager, params object[] conditionParas)
        {
            string sql = GetPagerSql(pager, conditionParas);
            List<Info> list = dbOper.Query<Info>(sql, conditionParas);
            return list;
        }

        private string GetPagerSql(PagerSetting pager, object[] conditionParas)
        {
            string where = "1=1";

            if (!string.IsNullOrEmpty(pager.Where))
            {
                where = pager.Where;
            }
            string orderby = _pkid;

            if (!string.IsNullOrEmpty(pager.OrderBy))
            {
                orderby = pager.OrderBy;
            }

            string sql = null;
            if (pager.PageSize == int.MaxValue)
            {
                sql = "select * from " + _tableName + " where " + where + " order by " + orderby;
            }
            else
            {
                pager.RecordCount = dbOper.GetScalar<int>("select count(*) from " + _tableName + " where " + where, conditionParas);
                int pageCount = pager.RecordCount / pager.PageSize;//总页数

                if (pager.RecordCount % pager.PageSize > 0)
                {
                    pageCount++;
                }
                if (pager.PageIndex < 1)
                {
                    pager.PageIndex = 1;
                }
                if (pager.PageIndex > pageCount)
                {
                    pager.PageIndex = pageCount;
                }

                int begin = (pager.PageIndex - 1) * pager.PageSize + 1;
                int end = begin + pager.PageSize - 1;
                sql = "select * from (select (row_number() over (order by " + orderby + ")) as RowID, * from " + _tableName + " where " + where + ") as a where RowID between " + begin + " and " + end;
            }
            return sql;
        }

        public virtual List<Info> GetList(PagerSortSetting pagerSetting)
        {
            return GetList(pagerSetting, null);
        }

        public virtual List<Info> GetList(PagerSortSetting pagerSetting, string condition, params object[] conditionParas)
        {
            string sql = GetPagerSql(pagerSetting, condition, conditionParas);
            List<Info> list = dbOper.Query<Info>(sql, conditionParas);
            return list;
        }

        private string GetPagerSql(PagerSortSetting pagerSetting, string condition, params object[] conditionParas)
        {
            if (condition == null || condition == "")
            {
                condition = "1=1";
            }
            string orderby = _pkid;

            if (pagerSetting.SortExpression != null && pagerSetting.SortExpression != "")
            {
                orderby = pagerSetting.SortExpression + " " + pagerSetting.SortDirection;
            }

            string sql = null;
            if (pagerSetting.PageSize == int.MaxValue)
            {
                sql = "select * from " + _tableName + " where " + condition + " order by " + orderby;
            }
            else
            {
                pagerSetting.RecordCount = dbOper.GetScalar<int>("select count(*) from " + _tableName + " where " + condition, conditionParas);
                //select * from (select (row_number() over (order by addtime desc)) as RowID, * from bank_back where " + condition + ") as a where RowID between(@PageIndex * @PageSize + 1) and (@PageIndex + 1) * @PageSize";
                sql = "select * from (select (row_number() over (order by " + orderby + ")) as RowID, * from " + _tableName + " where " + condition + ") as a where RowID between " + (pagerSetting.SkipCount + 1) + " and " + (pagerSetting.SkipCount + pagerSetting.PageSize);
                //sql = "select top " + pagerSetting.PageSize + " * from " + _tableName + " where " + _pkid + " not in (select top " + pagerSetting.SkipCount + " " + _pkid + " from " + _tableName + " where " + condition + " order by " + orderby + ") and " + condition + " order by " + orderby;
            }
            return sql;
        }

        public string GetName(int id)
        {
            return GetName((object)id);
        }

        public string GetName(string id)
        {
            return GetName((object)id);
        }

        private string GetName(object id)
        {
            string strSql = "select [" + TableField.Name + "] from " + _tableName + " where " + _pkid + " = @ID";
            SqlParameter para = new SqlParameter("@ID", id);
            IEnumerable<string> names = dbOper.Query<string>(strSql, para);
            if (names.Count() > 0)
            {
                return names.First();
            }
            else
            {
                return "";
            }
        }

        public virtual List<string> GetName<T>(IEnumerable<T> ids)
        {
            string strIDs = ConvertUtil.ConvertToDbStr(ids);
            string strSql = "select [" + TableField.Name + "] from " + _tableName + " where " + _pkid + " in (" + strIDs + ")";
            List<string> names = dbOper.Query<string>(strSql);
            return names;
        }
        public virtual Dictionary<T, string> GetIDName<T>(IEnumerable<T> ids)
        {
            Dictionary<T, string> keyValues = new Dictionary<T, string>();
            string strIDs = ConvertUtil.ConvertToDbStr(ids);
            string strSql = "select [" + TableField.ID + "],[" + TableField.Name + "] from " + _tableName + " where " + _pkid + " in (" + strIDs + ")";
            DataTable dt = dbOper.Query(strSql);
            foreach (DataRow row in dt.Rows)
            {
                keyValues.Add((T)row[TableField.ID.ToString()], (string)row[TableField.Name.ToString()]);
            }

            return keyValues;
        }

        public virtual bool Exist(string name, object id)
        {
            return ExistField(TableField.Name, name, id);
        }

        public virtual bool ExistCode(string code, object id)
        {
            return ExistField(TableField.Code, code, id);
        }

        public bool ExistField(TableField fieldName, string value, object id)
        {
            return ExistField(fieldName.ToString(), value, id);
        }

        public bool ExistField(string fieldName, string value, object id)
        {
            string strSql = "select count(*) from " + _tableName + " where " + fieldName + " = @p0";

            IEnumerable<int> count = null;
            if (id == null || id.ToString() == "")
            {
                count = dbOper.Query<int>(strSql, value);
            }
            else
            {
                strSql += " and " + _pkid + " != @p1";
                count = dbOper.Query<int>(strSql, value, id);
            }
            bool flag = (count.First() > 0) ? true : false;
            return flag;
        }

        public virtual void Save(Info info)
        {
            Type infoType = info.GetType();
            object id = infoType.GetProperty(_pkid).GetValue(info, null);
            bool isInsert = false;
            if (id is int || id is long)
            {
                if (Convert.ToInt64(id) == 0)
                {
                    isInsert = true;
                    infoType.GetProperty(_pkid).SetValue(info, GetMaxID(), null);
                }
            }
            else if (id is Guid)
            {
                if ((Guid)id == Guid.Empty)
                {
                    isInsert = true;
                    infoType.GetProperty(_pkid.ToString()).SetValue(info, Guid.NewGuid(), null);
                }
            }

            Save(info, isInsert);

        }

        /// <summary>
        /// 保存数据
        /// </summary>
        /// <param name="info"></param>
        /// <param name="isInsert">插入为true更新为false</param>
        public virtual void Save(Info info, bool isInsert)
        {
            Type infoType = info.GetType();
            BindingFlags flag = BindingFlags.Static | BindingFlags.NonPublic | BindingFlags.Public;
            string strSql = null;

            if (info is EntityBase)
            {
                EntityBase infoBase = info as EntityBase;
                strSql = isInsert ? infoBase.GetInsertSql() : infoBase.GetUpdateSql();

            }
            else//兼容老的
            {
                if (isInsert)
                {
                    strSql = (string)infoType.GetField(Constants.INSERT_SQL, flag).GetValue(null);
                }
                else
                {
                    strSql = (string)infoType.GetField(Constants.UPDATE_SQL, flag).GetValue(null);
                }
            }

            strSql = strSql.Replace("[-TableName-]", _tableName);

            Regex reg = new Regex(@"@[\w-]+");
            MatchCollection matchs = reg.Matches(strSql);

            List<string> lstParaNames = new List<string>();
            foreach (Match match in matchs)//去重
            {
                if (!lstParaNames.Contains(match.Value))
                {
                    lstParaNames.Add(match.Value);
                }
            }


            SqlParameter[] paras = new SqlParameter[lstParaNames.Count];
            for (int i = 0; i < lstParaNames.Count; i++)
            {

                string fieldName = lstParaNames[i].Trim('@');

                //兼容老的
                object value = (info is EntityBase) ? (info as EntityBase).GetValue(fieldName) : infoType.GetProperty(fieldName).GetValue(info, null);
                if (value == null)
                {
                    value = DBNull.Value;
                }
                else if (value is DateTime)
                {
                    if ((DateTime)value == DateTime.MinValue)
                    {
                        value = DBNull.Value;
                    }
                }
                paras[i] = new SqlParameter(lstParaNames[i], value);
            }

            dbOper.RunSql(strSql, paras);
        }

        public virtual Info GetInfo(object id)
        {
            if (id == null || id.ToString() == "")
            {
                Info info = new Info();
                return info;
            }

            string strSql = "select * from " + _tableName + " where " + _pkid + " = @p0";
            List<Info> infos = dbOper.Query<Info>(strSql, id);
            if (infos.Count > 0)
            {
                return infos.First();
            }
            else
            {
                return null;
            }
        }

        public int GetMaxID()
        {
            SqlParameter para = new SqlParameter("@TableName", _tableName);
            SqlParameter maxID = new SqlParameter();
            maxID.ParameterName = "@MaxID";
            maxID.SqlDbType = System.Data.SqlDbType.Int;
            maxID.Direction = System.Data.ParameterDirection.Output;

            dbOper.RunProc("GetTablePKID", para, maxID);

            return (int)maxID.Value;
        }

        public int GetMaxOrder()
        {
            string sql = "select isnull(max(Orders)+1,1) from " + _tableName;
            int order = dbOper.GetScalar<int>(sql);
            return order;

        }

        public int GetMaxCode()
        {
            string sql = "select isnull(max(Code)+1,1) from " + _tableName;
            int code = dbOper.GetScalar<int>(sql);
            return code;
        }

        #endregion

        public void SaveOrders(IEnumerable<int> ids, IEnumerable<int> values)
        {
            List<int> lstIDs = ids.ToList();
            List<int> lstValues = values.ToList();

            string[] sqls = new string[lstIDs.Count];

            for (int i = 0; i < lstIDs.Count; i++)
            {
                sqls[i] = "update " + _tableName + " set Orders=" + lstValues[i] + " where ID = " + lstIDs[i];
            }
            dbOper.RunSql(sqls);

        }

        #region ICommonOperate<Info> 成员


        private object GetIDByCode(object code)
        {
            string sql = "select " + _pkid + " from " + _tableName + " where " + TableField.Code + "=@p0";
            DataTable dt = dbOper.Query(sql, code);
            object id = null;
            if (dt.Rows.Count > 0)
            {
                id = dt.Rows[0][0];
            }
            return id;
        }
        public object GetIDByCode(int code)
        {
            return this.GetIDByCode((object)code);
        }

        public object GetIDByCode(string code)
        {
            return this.GetIDByCode((object)code);
        }
        #endregion

        public static T Instance<T>() where T : new()
        {
            Page page = (Page)System.Web.HttpContext.Current.CurrentHandler;
            string key = typeof(T).Name;

            if (page.Items[key] == null)
            {
                page.Items[key] = new T();
            }
            return (T)page.Items[key];
        }
    }
}
